/*****************************************************************************
*
*   Summary: Use SQL Event Notifications to automate enabling and
*            disabling SQL Agent jobs when a database mirroring failover
*            occurs changing the role of the servers.
*
*   Date: September 7, 2011
*
*   SQL Server Versions:
*         2005, 2008, 2008 R2
*        
******************************************************************************
*   Copyright (C) 2011 Jonathan M. Kehayias, SQLskills.com
*   All rights reserved.
*
*   For more scripts and sample code, check out
*      http://sqlskills.com/blogs/jonathan
*
*   You may alter this code for your own *non-commercial* purposes. You may
*   republish altered code as long as you include this copyright and give
*    due credit.
*
*
*   THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
*   ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
*   TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
*   PARTICULAR PURPOSE.
*
******************************************************************************/

USE [msdb]
GO

-- Create an Activation Procedure for the Queue
CREATE PROCEDURE [dbo].[ProcessDBMStateChangeEventNotifications]
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON
DECLARE @message_body xml
DECLARE @email_message nvarchar(MAX)
WHILE (1 = 1)
BEGIN
    -- Receive the next available message FROM the queue
    WAITFOR (
        RECEIVE TOP(1) -- just handle one message at a time
            @message_body=message_body
            FROM dbo.[MirroringStateChangeQueue]
    ), TIMEOUT 1000  -- if the queue is empty for one second, give UPDATE and go away
    -- If we didn't get anything, bail out
    IF (@@ROWCOUNT = 0)
        BEGIN
            BREAK
        END

    DECLARE @PreviousState INT, @NewState INT, @DatabaseID INT
   
    SELECT @PreviousState = @message_body.value('(/EVENT_INSTANCE/IntegerData)[1]', 'int' ),
            @NewState = @message_body.value('(/EVENT_INSTANCE/State)[1]', 'int' ),
            @DatabaseID = @message_body.value('(/EVENT_INSTANCE/DatabaseID)[1]', 'int' )
           
    --SELECT
    --CASE @PreviousState
    --    WHEN 0 THEN 'Null Notification'
    --    WHEN 1 THEN 'Synchronized Principal with Witness'
    --    WHEN 2 THEN 'Synchronized Principal without Witness'
    --    WHEN 3 THEN 'Synchronized Mirror with Witness'
    --    WHEN 4 THEN 'Synchronized Mirror without Witness'
    --    WHEN 5 THEN 'Connection with Principal Lost'
    --    WHEN 6 THEN 'Connection with Mirror Lost'
    --    WHEN 7 THEN 'Manual Failover'
    --    WHEN 8 THEN 'Automatic Failover'
    --    WHEN 9 THEN 'Mirroring Suspended'
    --    WHEN 10 THEN 'No Quorum'
    --    WHEN 11 THEN 'Synchronizing Mirror'
    --    WHEN 12 THEN 'Principal Running Exposed'
    --    WHEN 13 THEN 'Synchronizing Principal'
    --    ELSE 'Unknown'
    --END AS PreviousState
   
    DECLARE @jobname NVARCHAR(128)

    IF (@PreviousState = 0 AND @NewState = 13) -- Database Starting Up as Principal Role
    BEGIN
        -- Enable the Backup and Maintenance Jobs
        SELECT  @jobname = MIN(name)
        FROM msdb.dbo.sysjobs
        WHERE name NOT IN ('syspolicy_purge_history', 'Database Mirroring Monitor Job')

        WHILE @jobname IS NOT NULL
        BEGIN
            EXEC msdb.dbo.sp_update_job @job_name = @jobname, @enabled=1;

            SELECT  @jobname = MIN(name)
            FROM msdb.dbo.sysjobs
            WHERE name NOT IN ('syspolicy_purge_history', 'Database Mirroring Monitor Job')
              AND name > @jobname
        END
    END
   
    IF (@PreviousState IN (1,2) AND @NewState IN(7,8))  -- Failover has occurred
    BEGIN
        -- Disable the Backup and Maintenance Jobs
        SELECT  @jobname = MIN(name)
        FROM msdb.dbo.sysjobs
        WHERE name NOT IN ('syspolicy_purge_history', 'Database Mirroring Monitor Job')

        WHILE @jobname IS NOT NULL
        BEGIN
            EXEC msdb.dbo.sp_update_job @job_name = @jobname, @enabled=0;

            SELECT  @jobname = MIN(name)
            FROM msdb.dbo.sysjobs
            WHERE name NOT IN ('syspolicy_purge_history', 'Database Mirroring Monitor Job')
              AND name > @jobname
        END
    END

END
GO

--  Create a service broker queue to hold the events
CREATE QUEUE [MirroringStateChangeQueue]
    WITH STATUS=ON,
         ACTIVATION
            (PROCEDURE_NAME = [ProcessDBMStateChangeEventNotifications],
             MAX_QUEUE_READERS = 1,
             EXECUTE AS OWNER)
GO

--  Create a service broker service receive the events
CREATE SERVICE [MirroringStateChangeService]
    ON QUEUE [MirroringStateChangeQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO

CREATE ROUTE [MirroringStateChangeRoute]
    WITH SERVICE_NAME = 'MirroringStateChangeService',
    ADDRESS = 'LOCAL';
GO


CREATE EVENT NOTIFICATION [CaptureMirroringStateChanges]
    ON SERVER
    WITH FAN_IN
    FOR DATABASE_MIRRORING_STATE_CHANGE
    TO SERVICE 'MirroringStateChangeService', 'current database';
GO


/*
--  Cleanup objects
DROP EVENT NOTIFICATION CaptureMirroringStateChanges ON SERVER
DROP ROUTE MirroringStateChangeRoute
DROP SERVICE MirroringStateChangeService
DROP QUEUE MirroringStateChangeQueue
*/